USE [NANS21V]
GO
/****** オブジェクト:  StoredProcedure [dbo].[AnyTimeraceTeamList]    スクリプト日付: 03/17/2026 11:00:18 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[AnyTimeraceTeamList]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[AnyTimeraceTeamList]
GO

CREATE PROCEDURE [dbo].[AnyTimeraceTeamList]
	@TAIKAINO int,
	@SYUKEINO int
AS

Select 
	TK.KYOGIMEI,
	TR.KYOGINO,
	TR.SUBNO,
	TR.ROUNDNO,
	TR.KYOGISYANO,
	TR.KUMI,
	TR.LANE,
	SM.SYUMOKUKUBUN,
	-- 競技者情報
	L.SYUKKETU,
	KS.NUMBER1,
	KS.NUMBER2,
	(CONVERT(varchar, KS.KYOGISYAKANA) + '<br/>' + CONVERT(varchar, KS.KYOGISYAMEI)) AS KYOGISYAMEI,
	(CONVERT(varchar, S.SYOZOKUMEI) + '<br/>' + CONVERT(varchar, KS.KOJINSYOZOKUTIMEI)) AS SYOZOKUMEI,
	KS.GAKUNEN,
	KS.SEINEN,
	ROW_NUMBER() OVER (PARTITION BY L.SYUKKETU ORDER BY (CASE TR.JYUNI WHEN '0' THEN 2 END), TR.KIROKU) AS num,
	-- 記録
	dbo.Kiroku(
				RS.KIROKU 
			  , SM.SYUMOKUKUBUN
			  , SM.KIROKUKUBUN
			  , RS.EXTEND2
	) AS KIROKU,
	RS.KIROKU AS ORIGINAL_KIROKU,
	dbo.Jyuni(TR.JYUNI,SKG.OPENFLG) AS JYUNI,
	TR.PREJYUNI,
	dbo.CommentCodeConvert(TR.RCOMMENT, L.SYUKKETU) AS RCOMMENT,
	TR.KAZE,
	SKG.OPENFLG 
From TBL_ANY_TIMERACE_KEKKA as TR
	Left Join TBL_KYOGI as TK 
		on TR.TAIKAINO = TK.TAIKAINO and TR.KYOGINO = TK.KYOGINO 
	Left Join TBL_LANE as L 
		on L.TAIKAINO = TR.TAIKAINO and L.KYOGINO = TR.KYOGINO
		and TR.KYOGISYANO = L.KYOGISYANO and TR.KUMI = L.KUMINO 
		and TR.LANE = L.LANE and TR.ROUNDNO = L.ROUNDNO
	Left Join TBL_SANKAKYOGI as SKG
		on TR.TAIKAINO = SKG.TAIKAINO and L.KYOGINO = SKG.KYOGINO
		and TR.KYOGISYANO = SKG.KYOGISYANO
	Left Join TBL_KYOGI as KG
		on SKG.TAIKAINO = KG.TAIKAINO and SKG.KYOGINO = KG.KYOGINO
	Left Join TBL_KYOGISYA as KS
		on TR.TAIKAINO = KS.TAIKAINO and TR.KYOGISYANO = KS.KYOGISYANO
	Left Join TBL_SYOZOKU as S
		on TR.TAIKAINO = S.TAIKAINO and KS.SYOZOKUNO1 = S.SYOZOKUNO
	Left Outer Join TBL_SYUMOKU_MASTER as SM
		on SM.SYUMOKUNO = KG.SYUMOKUNO
	Left Outer Join TBL_RESULT as RS
		on  L.TAIKAINO = RS.TAIKAINO and L.KYOGINO = RS.KYOGINO
		and L.KUMINO = RS.KUMINO and L.LANE = RS.LANE and L.ROUNDNO = RS.ROUNDNO
Where TR.TAIKAINO = @TAIKAINO and TR.SYUKEINO = @SYUKEINO
Order By
	CASE L.SYUKKETU WHEN 2 THEN 2 ELSE 1 END,
	CASE SKG.OPENFLG WHEN 1 THEN 2 ELSE 1 END,
	CASE TR.JYUNI WHEN '0' THEN 2 ELSE 1 END,
	TR.JYUNI,TR.KIROKU,TR.KYOGISYANO